package persistencia;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import modelo.Grupo;

public class DAOGrupo {
	
	 private Connection conexao;
	    
	    private void obterConexao(){
	        try {
	            Class.forName("com.mysql.jdbc.Driver");
	            conexao = DriverManager.getConnection("jdbc:mysql://localhost/sirp", "root", "123");
	        } catch (ClassNotFoundException e) {
	            e.printStackTrace();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	    
	    private void liberarConexao(){
	        try {
	            conexao.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	    
	    public void inserir(Grupo grupo){
	    	obterConexao();
	    	try{
	    		 PreparedStatement pstat = conexao.prepareStatement("INSERT INTO Grupo (nome, tema, dataCriacao) VALUES (?, ?, ?)");
	             pstat.setString(1, grupo.getNome());
	             pstat.setString(2, grupo.getTema());
	             pstat.setString(3, grupo.getDataCriacao());
	             pstat.executeUpdate();
	             
	    	}catch (SQLException e) {
	                 e.printStackTrace();
	        }
	             liberarConexao();
	    }
	    
	    public void atualizar(Grupo grupo){
	    	obterConexao();
	    	try{
	    		PreparedStatement pstat=conexao.prepareStatement("UPDATE Grupo SET nome=?, tema=?, dataCriacao=?");
	    		pstat.setString(1, grupo.getNome());
	            pstat.setString(2, grupo.getTema());
	            pstat.setString(3, grupo.getDataCriacao());
	            pstat.executeUpdate();
	    	}catch (SQLException e) {
                e.printStackTrace();
	    	}
            liberarConexao();
	    	
	    }
	    
	    public void excluir(Grupo grupo){
	        obterConexao();
	        try {
	            Statement stat = conexao.createStatement();
	            stat.executeUpdate("DELETE FROM Grupo WHERE id = '" + grupo.getId() + "' ");
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        liberarConexao();
	    }
	    
	    public Grupo buscarGrupo(int id){
	    	 try {
	             PreparedStatement pstat = conexao.prepareStatement("SELECT * FROM Grupo WHERE id = ?");
	             pstat.setInt(1, id);
	             ResultSet set = pstat.executeQuery();
	             
	             if (set.next()){
	            	Grupo grupo= new Grupo();
	             	grupo.setNome(set.getString("nome"));
	             	grupo.setTema(set.getString("tema"));
	             	grupo.setDataCriacao(set.getString("dataCriacao"));
	             	
	             	pstat = conexao.prepareStatement("SELECT count(*) FROM Grupo WHERE id = ?");
	                pstat.setInt(1, id);
	                set = pstat.executeQuery();
	             	
	                return grupo;         	 
	                      	 
	             }
	             }  catch (SQLException e){
	            	 e.printStackTrace();
	             }
	             liberarConexao();
	             return null;

}
}